﻿using System;
using System.Linq;
using System.Data.Linq.Mapping;
using System.Data.Linq;

namespace TestLinqtoDb
{
    [Table(Name = "Employees")]
    public class Employee
    {
        [Column(IsPrimaryKey = true)]
        public int ID;
        [Column]
        public string Name;
        [Column]
        public string Department;
    }

    class MyDatabase : DataContext
    {
        private const String LoginString = @"Data Source=10.80.83.32\sql2005;Initial Catalog=kaaTestDB;User ID=sa;Password=sql2005";
        public Table<Employee> Employees;

        public MyDatabase()
            : base(LoginString)
        {
        }
    }

    
    class EmployeeTest
    {
        public void runTest()
        {
            MyDatabase db = new MyDatabase();

            //db.ExecuteCommand("CREATE TABLE Employees (ID int, Name varchar(50), Department varchar(50))");

            Employee employee1 = new Employee();
            employee1.ID = 101;
            employee1.Name = "John Smith";
            employee1.Department = "Sales";
            db.Employees.InsertOnSubmit(employee1);

            Employee employee2 = new Employee();
            employee2.ID = 102;
            employee2.Name = "Ted Black";
            employee2.Department = "Research";
            db.Employees.InsertOnSubmit(employee2);

            Employee employee3 = new Employee();
            employee3.ID = 103;
            employee3.Name = "Allen Gottlieb";
            employee3.Department = "Sales";
            db.Employees.InsertOnSubmit(employee3);

            db.SubmitChanges();

            var salesDept = from e in db.Employees
                            where e.Department == "Sales"
                            select e;

            foreach (var employee in salesDept)
                Console.WriteLine(employee.Name + " " + employee.ID);


            var queryResult = from e in db.Employees
                              where e.Name == "Ted Black"
                              select e;
            var tedBlack = queryResult.First();
            Console.WriteLine(tedBlack.ID);
        }
    }
}
